
from flask import Blueprint,render_template,request,redirect,session
from .model import db
from .utils import tools
from io import BytesIO
import json,base64,uuid,os


ad = Blueprint('admin',__name__,template_folder="templates", static_folder="static")


@ad.before_request
def check_login():
	if request.path in [
			'/',
			'/code',
			'/xxxxxxx',
			'/static/css/admin/bootstrap.min.css',
			'/static/css/admin/materialdesignicons.min.css',
			'/static/css/admin/style.min.css'
		]:
		return None

	if not session.get('user_admin'):
		return redirect('/')
	return None



#验证码生成器
@ad.route('/code')
def code():
	ts = tools()
	image, code = ts.get_verify_code()
	# 图片以二进制形式写入
	buf = BytesIO()
	image.save(buf, 'jpeg')
	buf_str = buf.getvalue()
	session['image'] = code
	return buf_str


#用户信息
def get_userinfo():
	username = session.get('user_admin')
	user_info = db.fetch_all('''select * from xxxxxxx where username='{}' '''.format(username,))
	if user_info:
		user_info = user_info[0]
	return user_info


#后台登陆	
@ad.route('/xxxxxxx',methods=['GET','POST'])
def xxxxxxx():
	if request.method =='GET':
		info = request.args.get('info')
		return render_template('admin/xxxxxxx.html',mes = info)
	formdata = request.form
	yzm_img = session.get('image').lower()

	yzm = formdata.get('yzm')
	username = formdata.get('username').replace(' ','')
	password = formdata.get('password')
	pwd = tools.password_md5(password)
	try:
		db_pwd = db.fetch_all('select pwd from t_xt_xxxxxxxuser where username="{}"'.format(username,))[0].get('pwd')
		if pwd ==db_pwd and  yzm == yzm_img :
			session['user_admin'] = username
			return redirect('/xxxxxxxx')
	except Exception as e:
		return redirect('/xxxxxxx?info=用户名密码错误！')
	return redirect('/xxxxxxx?info=用户名密码错误！')


#退出
@ad.route('/xxxxxxx')
def xxxxxxx():
	session.clear()
	return redirect('/')


#首页
@ad.route('/xxxxxxx')
def xxxxxxx():
	sql = '''
			select g.gn_id,g.gn_dm,g.gnmc,g.icon,g.sjgn_id,g.xh 
				from xxxxxxx r,xxxxxxx l,xxxxxxx b,t_xt_gn_admin g
			 where r.role_id = l.role_id
				 and l.role_id = b.role_id
				 and b.gn_id = g.gn_id 
				 and l.yxbz = "Y"
				 and g.yxbz = "Y"
				 and r.username = "{}" 
			 order by g.sjgn_id,g.xh
	'''
	username = session['user_admin']
	#根据登陆管理员的角色分配权限
	menu_list = db.fetch_all(sql.format(username,))
	user_info = get_userinfo()
	ctx = {}
	ctx['menu_list'] = menu_list
	ctx['user_info'] = user_info
	return render_template('admin/xxxxxxx.html', ctx = ctx )


#路由转发
@ad.route('/page_factory')
def page_factory():
	page_id = request.args.get('id')
	#新增和修改单独处理，其中由于要携带参数所以要按？号分割判断
	green_pass =  ['xxxxxxx','xxxxxxx','xxxxxxx','xxxxxxx','xt_user_xxxxxxxprofile']
	if page_id.split('?')[0] in green_pass:
		return redirect('/'+page_id)
	return render_template('xxxxxxx/'+page_id+'.html')



#BOOTSTRAP TABLE
@ad.route('/xxxxxxx')
def xxxxxxx():

	formdata = request.args
	fl = formdata.get('fl')
	country = formdata.get('country')
	need_name = formdata.get('need_name')
	shbz = formdata.get('shbz')
	rqq =formdata.get('rqq')
	rqz = formdata.get('rqz')
	sql = '''
		select uuid,need_name ,country,dic_info,intro,fl,rqq,rqz,yxbz 
					from xxxxxxx
					where country like '%{0}%' 
					and need_name like '%{1}%' 
				    and fl = '{2}'
					and rqz >= {3} 
					and rqq <= {4} 
					and yxbz='Y'
					and shbz='{5}'  order by need_name limit 100
		'''.format(country,need_name,fl,rqq,rqz,shbz)
	list_hzxx = db.fetch_all(sql)
	res = {}
	res['total'] = len(list_hzxx)
	res['rows'] = list_hzxx
	return json.dumps(res)


@ad.route('/post_getOne')
def post_getOne():

	uuid = request.args.get('uuid')

	sql = '''
		select uuid,need_name ,country,dic_info,intro,fl,rqq,rqz,yxbz ,detail
					from xxxxxxx
					where uuid='{}'
		'''.format(uuid,)
	res = db.fetch_all(sql)
	return json.dumps(res)


#删除资料信息
@ad.route('/hzxx_delete',methods =['GET','POST'])
def hzxx_delete():
	if request.method == 'GET':
		return redirect('/xxxxxxx')

	formdata = request.form
	uuid = formdata.get('uuid')
	sql = '''delete from xxxxxxx where uuid='{}' '''.format(uuid,)
	#删除记录
	db.update(sql)
	return json.dumps({'mes':'删除成功！'})


#修改资料信息
@ad.route('/xxxxxxx',methods =['GET','POST'])
def xxxxxxx():
	if request.method == 'GET':
		return redirect('/xxxxxxx')

	formdata = request.form
	fl = formdata.get('fl')
	gj = formdata.get('gj')
	name = formdata.get('name')
	rqq = formdata.get('rqq')
	rqz = formdata.get('rqz')
	intro = formdata.get('intro')
	detail = formdata.get('xxxx')
	uuid = formdata.get('uuid')

	sql = '''
		UPDATE xxxxxxx 
			SET need_name = '{0}',country = '{1}',
			intro = '{2}',fl = '{3}',rqq ={4},
			rqz ={5},shbz = 'Y',detail = '{6}' 
		WHERE	uuid = '{7}'
		'''.format(name,gj,intro,fl,rqq,rqz,detail,uuid)
	db.update(sql)
	return json.dumps({'mes':'修改成功！'})


#数据审核
@ad.route('/hzxx_sh',methods =['GET','POST'])
def hzxx_sh():
	if request.method == 'GET':
		return redirect('/xxxxxxxx')

	formdata = request.form
	uuid = formdata.get('uuid')
	sql = '''
		UPDATE t_hzxx 
			SET shbz = 'Y'
		WHERE	uuid = '{}'
		'''.format(uuid,)
	db.update(sql)
	return json.dumps({'mes':'审核成功！'})


#资料贡献审核查询
@ad.route('/xxxxxxx',methods =['GET','POST'])
def gl_zlgxsh():
	if request.method == 'GET':
		formdata = request.args
		fl = formdata.get('fl')
		spjg = formdata.get('spjg')
		rqq = formdata.get('rqq')
		rqz = formdata.get('rqz')
		sql = '''
			select 
				x.uuid,s.username,x.fl,x.country,x.need_name,rqq,rqz, DATE_FORMAT(x.lrrq,'%Y-%m-%d') lrrq,
				x.spjg,x.spyj,DATE_FORMAT(x.spsj, '%Y-%m-%d %H:%i:%s') spsj
			from xxxxxxx x,xxxxxxx s
			where x.user_id=s.id
			  and x.spjg='{0}'
			  and x.fl = '{1}'
			  and lrrq >=date'{2}'
			  and lrrq <=date'{3}'
			'''.format(spjg,fl,rqq,rqz)
		list_gxxx = db.fetch_all(sql)
		res = {}
		res['total'] = len(list_gxxx)
		res['rows'] = list_gxxx
		return json.dumps(res)


#资料贡献审核
@ad.route('/zlgx_sh',methods =['GET','POST'])
def zlgx_sh():

	if request.method == 'GET':
		uuid_gx = request.args.get('uuid')
		sql = '''select 
					uuid,need_name,country,intro,xxxx,fl,rqq,rqz,
					user_id,spjg,spyj,
					DATE_FORMAT(lrrq, '%Y-%m-%d') lrrq,
					DATE_FORMAT(spsj, '%Y-%m-%d %H:%i:%s') spsj
				from xxxxxxx where uuid ="{}"'''.format(uuid_gx,)
		res = db.fetch_all(sql)
		return json.dumps(res)

	formdata = request.form
	spjg = formdata.get('spjg')
	spyj = formdata.get('spyj')
	uuid = formdata.get('uuid')
	sql_sp ='''update xxxxxxx set spjg='{0}', spsj=SYSDATE(),spyj='{1}' where uuid ='{2}'
			'''
	if spjg =='N':
		db.update(sql_sp.format('02',spyj,uuid))
	else:
		db.update(sql_sp.format('01',spyj,uuid))
		sql_insert = '''
			insert into xxxxxxx  
				select uuid,need_name,country,'',intro,fl,rqq,rqz,'Y','Y',xxxx 
				from xxxxxxx 
				where uuid ='{}'
		'''
		db.insert_one(sql_insert.format(uuid,))
		from_path = '.\\pro_flask\\static\\tmp_img\\'
		to_path ='.\\pro_flask\\static\\images\\'
		sm_img_path='.\\pro_flask\\static\\sm_img\\'
		#贡献图片复制到生产地址
		tools.copyfile(from_path,to_path,uuid)
		#贡献图片压缩到小图地址
		tools.resize_image(from_path+uuid+'.jpg',sm_img_path+uuid+'.jpg')
	return json.dumps({'mes':'审核成功！'})



#系统管理-菜单管理
@ad.route('/qqqqqq')
def qqqqqq():

	query_data = request.args
	gnmc = query_data.get('gnmc')
	yxbz = query_data.get('yxbz')
	sql = '''
		select n.gn_id,n.gn_dm,n.gnmc,n.icon,n.xh, n.yxbz,
			(select a.gnmc from qqqqqq a where n.sjgn_id=a.gn_id ) sjgnmc
		from qqqqqq n where n.yxbz like '%{0}%' and n.gnmc like '%{1}%' order by n.sjgn_id,n.xh
	'''
	menu_list = db.fetch_all(sql.format(yxbz,gnmc))
	res = {}
	res['total'] = len(menu_list)
	res['rows'] = menu_list
	return json.dumps(res)
	



#删除菜单
@ad.route('/qqqqqq',methods =['GET','POST'])
def xt_menu_delete():
	if request.method =='GET':
		return redirect('/qqqqqq')

	gn_id = request.form.get('gn_id')
	res = db.fetch_all('select 1 from qqqqqq where sjgn_id={}'.format(gn_id))
	mes = ''
	if res:
		mes ='存在子菜单不允许删除！'
	else:
		db.update('delete from qqqqqq where gn_id={}'.format(gn_id))
		mes ='删除成功！'
	return json.dumps({'mes':mes})


#新增菜单
@ad.route('/xt_menu_add',methods =['GET','POST'])
def xt_menu_add():
	if request.method =='GET':
		return redirect('/xt_menu')

	data = request.form
	gn_dm = data.get('gn_dm')
	gnmc = data.get('gnmc')
	icon = data.get('icon')
	sjgn_id = data.get('sjgn_id')
	yxbz = data.get('yxbz')
	xh = data.get('xh')
	ifexists = db.fetch_all('select 1 from qqqqqq where gn_dm="{}"'.format(gn_dm,))
	if ifexists:
		return json.dumps({'mes':'新增失败，功能代码不能重复！'})
	try:
		max_gn_id = db.fetch_all('select max(gn_id) gn_id from qqqqqq where ifnull(sjgn_id,"")="{}"'.format(sjgn_id,))[0].get('gn_id')
		#新增菜单
		if not sjgn_id:
			#如果是目录则功能ID往后排
			gn_id = max_gn_id + 10000000
			sql = 'INSERT INTO qqqqqq VALUES ({0}, "{1}", "{2}", "{3}", null, {4}, sysdate(), "Y")'.format(gn_id,gn_dm,gnmc,icon,xh)
			db.insert_one(sql)
		elif not max_gn_id:
			gn_id = int(sjgn_id) + 1
			sql = 'INSERT INTO qqqqqq VALUES ({0}, "{1}", "{2}", "{3}", {4}, {5}, sysdate(), "Y")'.format(gn_id,gn_dm,gnmc,icon,sjgn_id,xh)
			db.insert_one(sql)
		else:
			gn_id = max_gn_id + 1
			sql = 'INSERT INTO qqqqqq VALUES ({0}, "{1}", "{2}", "{3}", {4}, {5}, sysdate(), "Y")'.format(gn_id,gn_dm,gnmc,icon,sjgn_id,xh)
			db.insert_one(sql)
	except Exception as e:
		return json.dumps({'mes':'新增失败，请检查内容再提交！'+str(e)})
	return json.dumps({'mes':'新增成功！'})


#修改菜单
@ad.route('/xt_menu_edit',methods =['GET','POST'])
def xt_menu_edit():
	if request.method =='GET':
		gn_id = request.args.get('gn_id')
		res = db.fetch_all('select gn_id,gn_dm,gnmc,icon,sjgn_id,xh,yxbz from qqqqqq where gn_id="{}"'.format(gn_id,))[0]
		return json.dumps(res)
	formdata = request.form
	gn_id = formdata.get('gn_id')
	gn_dm = formdata.get('gn_dm')
	gnmc = formdata.get('gnmc')
	icon = formdata.get('icon')
	sjgn_id = formdata.get('sjgn_id')
	yxbz = formdata.get('yxbz')
	xh = formdata.get('xh')
	#仅提供名称，图标，状态，序号的修改
	sql = 'update qqqqqq set gnmc="{0}",icon="{1}",yxbz="{2}",xh="{3}" where gn_id={4}'.format(gnmc,icon,yxbz,xh,gn_id)
	db.update(sql)
	return json.dumps({'mes':'修改成功！'})



#修改菜单
@ad.route('/qqqqqq')
def xt_menu_getIcon():
	res = db.fetch_all('select * from qqqqqq order by rand() limit 20')
	return json.dumps(res)



#系统用户管理bootstrap table 请求
@ad.route('/xt_user')
def xt_uqqqqqqser():

	query_data = request.args
	username = query_data.get('username')
	rolemc = query_data.get('rolemc')
	sql = '''
		select r.*,l.rolemc from t_xt_user r left join qqqqqq l on r.role_id = l.role_id 
						   where r.username like '%{0}%' and ifnull(l.rolemc,"") like '%{1}%'
	'''
	user_list = db.fetch_all(sql.format(username,rolemc))
	res = {}
	res['total'] = len(user_list)
	res['rows'] = user_list
	return json.dumps(res)


#系统用户管理-删除
@ad.route('/xt_user_delete')
def xt_user_delete():
	manager_id = request.args.get('manager_id')
	db.update('delete from qqqqqq where manager_id={}'.format(manager_id))
	return json.dumps({'mes':'删除成功！'})


#系统用户管理-新增
@ad.route('/xt_user_add',methods =['GET','POST'])
def xt_user_add():
	if request.method =='GET':
		res = db.fetch_all('select * from t_xt_role')
		ctx ={}
		ctx['res'] = res
		return render_template('admin/xt_user_add.html',ctx = ctx)
	user_data = request.form
	username = user_data.get('username')
	pwd = tools.password_md5(user_data.get('pwd'))
	xm = user_data.get('xm')
	tel = user_data.get('tel')
	yxbz = user_data.get('yxbz')
	role_id = user_data.get('role_id')
	sql = 'insert into t_xt_user values(manager_id,"{0}","{1}","{2}","{3}","{4}","{5}")'
	try:
		db.insert_one(sql.format(username,pwd,xm,tel,role_id,yxbz))

	except Exception as e:
		return  json.dumps({'mes':'添加失败！'+str(e)})
	return  json.dumps({'mes':'添加成功！'})


#系统用户管理-修改
@ad.route('/xt_user_edit/',methods =['GET','POST'])
def xt_user_edit():
	if request.method =='GET':
		manager_id = request.args.get('uid')
		role = db.fetch_all('select role_id,rolemc from t_xt_role')
		manager = db.fetch_all('select * from t_xt_user where manager_id={}'.format(manager_id,))[0]
		ctx ={}
		ctx['role'] = role
		ctx['manager'] = manager
		return render_template('admin/xt_user_edit.html',ctx = ctx)
	user_data = request.form
	manager_id = user_data.get('manager_id')
	xm = user_data.get('xm')
	tel = user_data.get('tel')
	yxbz = user_data.get('yxbz')
	role_id = user_data.get('role_id')
	sql = 'update t_xt_user set xm="{0}",tel="{1}",role_id="{2}",yxbz="{3}" where manager_id="{4}"'
	try:
		db.update(sql.format(xm,tel,role_id,yxbz,manager_id))
	except Exception as e:
		return  json.dumps({'mes':'修改失败！'+str(e)})
	return  json.dumps({'mes':'修改成功！'})



#系统用户管理-密码重置
@ad.route('/xt_user_pwdreset',methods =['GET','POST'])
def xt_user_pwdreset():
	if request.method =='GET':
		return render_template('/xxxxxxxx')
	manager_id = request.form.get('manager_id')
	pwd = tools.password_md5('Admin_123')
	sql = 'update t_xt_user set pwd = "{0}" where manager_id="{1}"'
	try:
		db.update(sql.format(pwd,manager_id))
	except Exception as e:
		return  json.dumps({'mes':'重置失败！'+str(e)})
	return  json.dumps({'mes':'重置成功！'})


#个人信息
@ad.route('/qqqqqq',methods =['GET','POST'])
def xt_user_profile():
	user_info = get_userinfo()
	if request.method =='GET':
		return render_template('admin/xt_useqqqqqqr_profile.html',ctx =user_info)

	print(request.form)
	xm = request.form.get('xm')
	tel = request.form.get('tel')
	sql = 'update qqqqqq set xm="{0}" ,tel="{1}"'.format(xm,tel)
	try:
		db.update(sql)
	except Exception as e:
		pass 	
	return  redirect('/xxxxxxxx')



#个人信息-密码修改
@ad.route('/xt_user_pwdmodify',methods =['GET','POST'])
def xt_user_pwdmodify():
	if request.method =='GET':
		return redirect('/xxxxxxxx')

	login_user = get_userinfo()
	oldpwd = tools.password_md5(request.form.get('oldpwd'))
	newpwd = request.form.get('newpwd')
	conpwd = request.form.get('conpwd')

	if oldpwd == login_user.get('pwd') and newpwd == conpwd and  newpwd:
		sql = 'update qqqqqq set pwd = "{0}" where manager_id="{1}"'
		newpwd = tools.password_md5(newpwd)
		db.update(sql.format(newpwd,login_user.get('manager_id')))
		return  json.dumps({'mes':'修改成功！'})
	
	return  json.dumps({'mes':'修改失败！'})



#系统用户管理bootstrap table 请求
@ad.route('/xt_role')
def xt_role():

	query_data = request.args
	rolemc = query_data.get('rolemc')
	sql = '''
		select * from t_xt_role where rolemc like '%{}%'
	'''
	role_list = db.fetch_all(sql.format(rolemc,))
	res = {}
	res['total'] = len(role_list)
	res['rows'] = role_list
	return json.dumps(res)


#系统用户管理-删除
@ad.route('/xt_role_delete')
def xt_role_delete():
	role_id = request.args.get('role_id')
	try:
		db.update('delete from t_xt_role where role_id={}'.format(role_id))
		db.update('delete from t_xt_rolegndzb where role_id={}'.format(role_id))
	except Exception as e:
		return json.dumps({'mes':'删除失败！'+str(e)})
	return json.dumps({'mes':'删除成功！'})


#系统角色管理-新增
@ad.route('/xt_role_add',methods =['GET','POST'])
def xt_role_add():
	if request.method =='GET':
		sql = '''
			select gn_id,gnmc,case when sjgn_id is null then 'root' else 'leaf' end isroot,left(gn_id,2) id_1,RIGHT(gn_id,2) id_2 
				from t_xt_gn_admin where yxbz="Y" order by gn_id,xh
		'''
		res = db.fetch_all(sql)
		return render_template('admin/xt_role_add.html',res=res)

	rolemc = request.form.get('rolemc')
	bz = request.form.get('bz')
	yxbz = request.form.get('yxbz')
	gnid_list = request.form.getlist('gn_list[]')
	isExists = db.fetch_all('select 1 from t_xt_role where  rolemc="{}"'.format(rolemc,))
	#ID递增1
	role_id = db.fetch_all('select max(role_id) role_id from t_xt_role')[0].get('role_id')+1
	if isExists:
		return json.dumps({'mes':'角色名称已存在！'})

	try:
		db.insert_one('insert into t_xt_role values({0},"{1}","{2}","{3}")'.format(role_id,rolemc,bz,yxbz))
		for gn_id in gnid_list:
			db.insert_one('insert into t_xt_rolegndzb values(replace(uuid(),"-",""),"{0}","{1}")'.format(role_id,gn_id))
	except Exception as e:
		return  json.dumps({'mes':'添加失败！'+str(e)})
	return  json.dumps({'mes':'添加成功！'})


#系统角色管理-修改
@ad.route('/xt_role_edit/',methods =['GET','POST'])
def xt_role_edit():
	if request.method =='GET':
		role_id = request.args.get('uid')
		try:
			role = db.fetch_all('select * from t_xt_role where role_id={}'.format(role_id,))[0]
			sql = '''
				 select  n.gn_id,n.gnmc,
					 case when n.sjgn_id is null then 'root' else 'leaf' end isroot,
					 left(n.gn_id,2) id_1, RIGHT(n.gn_id,2) id_2, a.gn_id checked
				 from t_xt_gn_admin n 
				   left join (select gn_id from t_xt_rolegndzb where role_id="{}") a
					 on n.gn_id = a.gn_id
				 where n.yxbz="Y"  order by n.gn_id,n.xh
			'''
			gn_list = db.fetch_all(sql.format(role_id,))
		except Exception as e:
			print(e)

		ctx ={}
		ctx['role'] = role
		ctx['gn_list'] = gn_list
		return render_template('admin/xt_role_edit.html',ctx = ctx)

	role_id = request.form.get('role_id')
	bz = request.form.get('bz')
	yxbz = request.form.get('yxbz')
	gnid_list = request.form.getlist('gn_list[]')

	try:
		db.update('update t_xt_role set bz="{0}",yxbz="{1}" where role_id="{2}"'.format(bz,yxbz,role_id))
		#删除原值后插入
		db.update('delete from t_xt_rolegndzb where role_id="{}"'.format(role_id,))
		for gn_id in gnid_list:
			db.insert_one('insert into t_xt_rolegndzb values(replace(uuid(),"-",""),"{0}","{1}")'.format(role_id,gn_id))
	except Exception as e:
		return  json.dumps({'mes':'修改失败！'+str(e)})
	return  json.dumps({'mes':'修改成功！'})









